Archive Magazine CD 1995/Archive Magazine CD 1995.iso/discs/pipeline/abacus/p_line/Sheets2/ReadMe

Text File | 1991-12-30 | 9.8 KB | 204 lines

%OP%VS4.11 (04-Dec-91), Gerald Lewis Fitton, R4000 5065 0380 9644
%OP%DP0
%OP%IRY
%OP%PL0
%OP%HM0
%OP%FM0
%OP%BM0
%OP%LM4
%OP%FX
%OP%FY
%OP%FS
%OP%PT1
%OP%PDPipeLine
%OP%WC834,2070,148,1620,0,0,0,0
%CO:A,72,72%
%C%Spreadsheets from the Beginning - Part 2
%C%by Gerald L Fitton
Keywords:
Beginners Spreadsheet Eats Fitton

Where was Part 1?
The first part of this series, 'Spreadsheets from the Beginning', is
in the directory Sheets1; read that first.

You Are What You Eat
In this tutorial you are going to create a spreadsheet which will
calculate the number of calories in a main course meal.  As you do this
you will learn about the pop-up menus and function keys, setting column
widths, adding columns and inserting rows.  Your first target is the
sheet [Eats01] which you will find in this directory.  Load it in now
and have a quick look at it.  I suggest that you print it out so that
you can easily refer to it while you work through this tutorial.

Alternatively, leave it in a window on screen, or use both!

A New Blank Sheet
Click select on the installed !Pipedream icon.

The Pop-Up Menus
If you have the full version (not the Pipedream demonstration disc)
then you can Save and Rename the sheet with one operation.  One way of
doing this is to use '<CTRL FS>'.  The alternative, which I'm going to
describe now is to use the pop-up menus.

Click the mouse 'menu' (centre) button and a menu appears.  Run the
pointer through Files and click 'select' on Save.  Change the name in
the dialogue box to [Eats01a] (the 'a' is so that you don't confuse
your sheet, [Eats01a], with the one on the disc, [Eats01]) and then
drag the PipeDream icon into an open directory viewer window.  You have
saved the sheet as [Eats01a] and you will find the name has changed in
the title window of the sheet at the same time.

The Function keys
Most commands can be executed in any of three ways: a <Ctrl> key
sequence, the pop-up menus or by using the red or grey function keys,
marked F1 to F10.  As a beginner you will almost certainly find the
pop-up menus easiest because every command is available this way.  As
you get more proficient you will begin to use the function keys for the
most commonly used commands, and finally, as an expert I expect you
will use the <Ctrl> key sequences as short-cuts.

Add a Column
It doesn't matter where you are in the sheet (ie where the cursor is),
holding down <Ctrl> and tapping the keys EAC key will add a column to
the right of the existing sheet (I shall call this <CTRL EAC>).  Add
columns G and H this way.

Change the Column Widths
Your objective is to change the column widths so that the sheet looks
like [Eats01].  Click the pointer on the A at the top of column A to
place the cursor in column A.  You can use the Layout - Set column
width pop-up menu (or <Ctrl W>) to change the width of column A, but it
is generally quicker to use the mouse to drag the edge of the column;
you do this by placing the pointer exactly on the line between the A
and B at the top of the column.  If you look at the Mark block box
whilst you have the select button depressed you will see a number
displayed.  This is the column width in characters.  Drag the right
hand edge of column A until 9 appears in the mark block box; then
release the select button.  Click select on the letter B to move the
caret into the B column and change its width to 4.  The other columns,
C to H have widths of: 11, 6, 16, 8, 6 and 9.

Set the Right Margin
'<CTRL H>' (Horizontal extent) can be used on each column individually.
Use it to set the right margin of column A at 69.  If you have made all
the columns the correct width then you will find that the short
vertical (right margin) arrow is just to the left of the letter H.
Setting the right margin this way sets only one of the columns.  Click
on B and you will (probably) find the arrow in a different place.  You
could repeat the '<CTRL H>' sequence for all seven other columns but it
is quicker to set them all with one operation.  Click on A, then hold
down '<Ctrl>' and tap first 'LML' followed by 'LMR'.  The arrow will
move first to the left and then back to the right.  You might think
that nothing has changed but click select on B, C, D, E, F, G and H and
you will find that the right margin arrow is always just left of the
'H'.  All the right margins have been set by the one operation.

Saving the Sheet
Save the file if you can.  You don't need to drag the icon into the
directory viewer the second time you Save.  Just click on OK or press
<Return>.  Save as often as you remember it and certainly now that you
have successfully reached a staging post.  When I'm writing articles I
Save after completing every paragraph.

Loading a Sheet
If you make a big mistake you can always reload the sheet; you did save
it didn't you?  Kill the ruined sheet by clicking on the close box (the
'x' near the top left of the sheet).  Double click on the sheet icon in
the directory viewer and it will reload.

The Conversion Factors
There is a major difference in philosophy between Algebra and Computing
which might be put as "In Computing, never use a constant when you can
use a variable".  In Algebra it is usual to eliminate as many variables
as possible from the problem by turning them into values; in Computing,
the flexibility of a spreadsheet is usually enhanced by putting the
numerical value of an often used constant into one cell and then
writing formula which refer to that cell.  This technique has the
advantage that, if the value in the cell changes, or becomes known to a
greater accuracy (how many grammes are there in a teaspoonful?) then,
as we shall see, changing the one value in the cell (cell 'C4') will
cause a value change in all the cells which, as part of a formula,
contain reference to that one cell.

Complete rows 1 to 6 of the table as shown in [Eats01].  Here's a tip
to save you some time.  In cell 'B3' type =.  Now drag the pointer
(hold down the select button whilst you move the mouse) over slots B3
to B6.  This marks the whole range B3B6.  Now you can replicate the
equals sign through the marked block using <CTRL BRD> (Block Replicate
Down) or the same command from within the Blocks pop-up menu.  Click in
the Mark block box (just under the Pipedream 4 logo at the top of the
sheet) to clear the marked block.

Decimal Places
Mark the block 'C3' to 'C6' followed by Layout - Decimal Places or
'<CTRL LDP>'.  Use the dialogue box to set 6 decimal places for every
cell within the marked block.

Calculating the Calories
Complete the [Eats01] spreadsheet.  All the cells contain values which
are typed in as they are displayed on the sheet except cells 'H10' to
'H15' and 'H17' which contain formulae.

Use Block Replicate Down to fill 'B10' to 'B15' with per.

The cell 'H10' contains the formula F10*A10/C10*C3.  The value of
A10/C10 is the number of calories per gm.  This is multiplied by C3 to
convert it to calories per oz.  Finally, you multiply by F10 to obtain
the total number of calories in the 6 oz.  Of course, multiplication is
'commutative' (ie it doesn't matter in what order you multiply and
divide) so the order can be changed.  Notice that the mixture of
multiplication and division is evaluated from left to right so that
F10*A10/C10*C3 means (F10*A10/C10)*C3 and not F10*A10/(C10*C3).  As you
enter the formulae use the appropriate conversion factor from the set
'C3' to 'C6'.  Check by looking at the values in 'Eats01'.

I think that the spreadsheet looks tidier if cells 'A10' to 'A15' and
those in columns C and H are set to 0 decimal places and cells 'F10' to
'F15' to 2 decimal places.

Total a Column
The formula in 'H17' is Sum(H9H16).  This Sum function saves you typing
in something such as H9+H10+H11+H12+H13+H14+H15+H16 which might get a
bit tedious if you want to add up a couple of hundred (or thousand)
numbers.  The combination H9H16 is called a Range of cells.  By the
way, I haven't got the range wrong.  I know that rows 9 and 16 are
blank; including them is deliberate as we'll see in another article
when you come to Sort a block which is summed.

Inserting a New Row
The spreadsheet as it stands gives you a main course of six items.
Suppose you want to add some more items to the menu so the sheet looks
like [Eats02].  Load this now and have a look at it.

Add the Chips between the Baked Potatoes and the Carrots.  To do this
place the cursor anywhere in the row containing the Carrots, row 13.
Now use the Edit - Insert Row (or press key F7) and a new row will
appear.  What's happened to all the formulae?  In particular, what's
happened to the formula which was in 'H17' and is now in 'H18'?  If all
has gone well you should find that the Sum in 'H18' covers the range
'H9H17' - in fact all the cell references have been 'updated' to match
the new layout.  Clever isn't it?  If you enter the values for Chips
shown in [Eats02] and a formula in column H then the calories due to
the Chips will be added to the total in 'H18'.

What If
You may want three teaspoonsful of Ketchup.  Place the cursor in cell
'F16', type 3 and press '<Return>'.  When you change values in column F
you will find that the corresponding values in column H and the total
in 'H21' also change.

You may find more accurate data than the values I have given in column
A; change them.  Perhaps you use a larger (or smaller) teaspoon for
your Ketchup; if so then change the value in cell 'C4'.  The values in
'H16' and 'H21' will change again.

What Do You Eat?
Insert a few rows and add in something which suits your own diet.  You
can delete any of the rows you don't like with <F8> and the Sum(range)
will change to match the number of rows you have.  By the way, if you
didn't believe me about the range having to include a couple of blank
rows then see what happens to your Sum(range) if you delete either the
first or last row of your range!